class Category < ActiveRecord::Base
  has_and_belongs_to_many :drugs

  def self.find_by_initial(initial)
    find(:all, :conditions => ["name like ?", initial + "%"], :order => "name ASC")
  end

  def self.find_drugs(category_id)
    find(category_id).drugs.sort {|x, y| x.name <=> y.name}
  end

  def get_rating_number(drug_list, sex, age, time_taken)
    if drug_list.blank?
      drugs_filter = "1"
    else
      drugs_filter = drug_list.collect {|d| "or r.drug_id = #{d.to_i}" unless d.to_i == 0}.join(" ")
      drugs_filter = drugs_filter[3..-1]
    end
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        else
                          "1"
                        end
    sql = <<-SQL
      select count(distinct r.id) cnt
      from categories_drugs cd, ratings r 
      where
      r.drug_id = cd.drug_id
      and (#{drugs_filter})
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
    SQL
    connection.select_all(sql)[0]["cnt"].to_i
  end
  
  def self.get_details(sideffect, drug_list, sex, age, time_taken, order = nil)
    order_filter = "order by d.name asc"
    order_filter = "order by cnt asc" if order == "frequency"
    if drug_list.blank?
      drugs_filter = "1"
    else
      drugs_filter = drug_list.collect {|d| "or r.drug_id = #{d.to_i}" unless d.to_i == 0}.join(" ")
      drugs_filter = drugs_filter[3..-1]
    end
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        else
                          "1"
                        end
    sql = <<-SQL
      select d.id id, d.name name, count(distinct r.id) cnt
      from drugs d, ratings r, ratings_sideffects rs
      where r.drug_id = d.id
      and rs.rating_id = r.id
      and (#{drugs_filter})
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
      and rs.sideffect_id = #{sideffect}
      group by d.id
      #{order_filter}
    SQL
    connection.select_all(sql) 
  end

  def self.get_sideffects(drug_list, sex, age, time_taken, order = nil)
    order_filter = "order by cnt desc"
    order_filter = "order by name asc" if order == "name"
    drugs_filter = drug_list.collect {|d| "or r.drug_id = #{d.to_i}" unless d.to_i == 0}.join(" ")
    drugs_filter = drugs_filter[3..-1]
    drugs_filter = "r.id = 0" if drugs_filter.blank?
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        else
                          "1"
                        end
    sql = <<-SQL
      select s.id id, s.name name, count(distinct r.id) cnt
      from sideffects s, ratings r force index(index_ratings_on_drug_id_and_id), ratings_sideffects rs
      where r.id = rs.rating_id
      and s.id = rs.sideffect_id
      and (#{drugs_filter})
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
      group by s.id 
      #{order_filter}
    SQL
    connection.select_all(sql) 
  end
  
end
